package cn.basic.batch.service;

import cn.basic.batch.vo.UserAddress;
import com.google.common.base.Stopwatch;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.TimeUnit;

/**
 * @description:
 * @author: ZZZ
 * @time: 2021/3/29 14:46
 */
@Component
public class BatchPsbUserAddressComponent {
    @Autowired
    private JdbcTemplate jdbcTemplate;



    /**
     * @param fqid
     * @name: batchPsbUserAddressByUserId
     * @description: psb_user_address 新增子用户地址
     * psb_user_address和psb_user关联：userid   psb_user和psb_user_child关联：id、pid、ext_part_id、part_id
     * @return: void
     * @auther: ZZZ
     * @date: 2021/3/26 17:15
     */
    public Long batchPsbUserAddress(int fqid) {
        return jdbcTemplate.execute(new ConnectionCallback<Long>() {
            @Override
            public Long doInConnection(Connection connection) throws SQLException, DataAccessException {
                Stopwatch stopwatch = Stopwatch.createStarted();
                connection.setAutoCommit(Boolean.TRUE);
                Long totalAll = 0L;
                Integer total = null;

                PreparedStatement pstmt = connection.prepareStatement(getQueryAddressSql());
                ResultSet rs = null;
                Long id = null;


                Boolean swich = true;
                id = 0L;
                total = 0;
                while (swich) {
                    total = 0;
                    pstmt.setInt(1, fqid);
                    pstmt.setLong(2, id);
                    rs = pstmt.executeQuery();

                    List<UserAddress> userAddressList = new ArrayList<>();
                    while (rs.next()) {
                        total = total + 1;

                        //查询子用户的userid
                        pstmt = connection.prepareStatement(getChildUserIdSql());
                        //psb_user_address 集合
                        getUserAddressList(pstmt, rs, userAddressList);


                        if (userAddressList != null && userAddressList.size() > 0) {
                            //批量插入psb_user_address
                            int[] insertResult = jdbcTemplate.batchUpdate(getInsertAddressSql(), new BatchPreparedStatementSetter() {
                                @Override
                                public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                                    preparedStatement.setString(1, userAddressList.get(i).getName());
                                    preparedStatement.setString(2, userAddressList.get(i).getPhone());
                                    preparedStatement.setString(3, userAddressList.get(i).getPostcode());
                                    preparedStatement.setString(4, userAddressList.get(i).getProvinceName());
                                    preparedStatement.setString(5, userAddressList.get(i).getCityName());

                                    preparedStatement.setString(6, userAddressList.get(i).getCountyName());
                                    preparedStatement.setString(7, userAddressList.get(i).getProvinceCode());
                                    preparedStatement.setString(8, userAddressList.get(i).getCityCode());
                                    preparedStatement.setString(9, userAddressList.get(i).getCountyCode());
                                    preparedStatement.setString(10, userAddressList.get(i).getAddressDetail());

                                    preparedStatement.setBoolean(11, userAddressList.get(i).getIsDefault());
                                    preparedStatement.setString(12, userAddressList.get(i).getUserId());
                                    preparedStatement.setInt(13, userAddressList.get(i).getStatus());
                                    preparedStatement.setTimestamp(14,new Timestamp(userAddressList.get(i).getCreateTime().getTime()));
                                    preparedStatement.setString(15, userAddressList.get(i).getCreateUser());

                                    preparedStatement.setTimestamp(16, new Timestamp(userAddressList.get(i).getOpTime().getTime()));
                                    preparedStatement.setString(17, userAddressList.get(i).getOpUser());
                                    preparedStatement.setInt(18, userAddressList.get(i).getPartId());

                                }

                                @Override
                                public int getBatchSize() {
                                    return userAddressList.size();
                                }
                            });
                            //记录操作数
                            for (int i = 0; i < insertResult.length; i++) {
                                totalAll = totalAll + insertResult[i];
                            }
                        }
                    }
                    swich = (total - 400) == 0;
                    if (rs != null) {
                        rs.close();
                    }
                }
                pstmt.close();
                connection.close();
                System.out.println("处理[psb_user_opentime][{}]完成,共耗时{}s "+ totalAll+" "+stopwatch.elapsed(TimeUnit.SECONDS));
                return totalAll;
            }

            //psb_user_address list
            private List<UserAddress> getUserAddressList(PreparedStatement pstmt, ResultSet rs, List<UserAddress> addressList) throws SQLException {
                ResultSet resultSet = null;
                pstmt.setLong(1, rs.getLong("pu_id"));
                pstmt.setInt(2, rs.getInt("pu_ext_part_id"));
                resultSet = pstmt.executeQuery();
                while (resultSet.next()) {
                    UserAddress userAddress = new UserAddress();
                    //psb_user_child
                    userAddress.setUserId(resultSet.getString("child_user_id"));
                    //psb_user_address 原始数据
                    userAddress.setName(rs.getString("name"));
                    userAddress.setPhone(rs.getString("phone"));
                    userAddress.setPostcode(rs.getString("postcode"));
                    userAddress.setProvinceName(rs.getString("province_name"));
                    userAddress.setCityName(rs.getString("city_name"));
                    userAddress.setCountyName(rs.getString("county_name"));
                    userAddress.setProvinceCode(rs.getString("province_code"));
                    userAddress.setCityCode(rs.getString("city_code"));
                    userAddress.setCountyCode(rs.getString("county_code"));
                    userAddress.setAddressDetail(rs.getString("address_detail"));
                    userAddress.setIsDefault(rs.getBoolean("is_default"));
                    userAddress.setStatus(rs.getObject("status") != null ? rs.getInt("status") : null);
                    userAddress.setCreateTime(rs.getTimestamp("create_time"));
                    userAddress.setCreateUser(rs.getString("create_user"));
                    userAddress.setOpTime(rs.getTimestamp("op_time"));
                    userAddress.setOpUser(rs.getString("op_user"));
                    userAddress.setPartId(rs.getObject("part_id") != null ? rs.getInt("part_id") : null);

                    addressList.add(userAddress);
                }
                resultSet.close();
                return addressList;

            }

            //查询sql
            private String getQueryAddressSql() {
                StringBuffer sb = new StringBuffer();
                sb.append("select pu.ext_part_id pu_ext_part_id,pu.id pu_id,pa.* ");
                sb.append("from psb_user pu,psb_user_address pa ");
                sb.append("where pu.user_id=pa.user_id ");
                sb.append("and pa.part_id = ? and pa.address_id > ? ");
                sb.append("order by pa.address_id limit 400");
                return sb.toString();
            }

            //查询子用户的userid
            private String getChildUserIdSql() {
                StringBuffer sb = new StringBuffer();
                sb.append("select b.user_id child_user_id ");
                sb.append("from psb_user a,psb_user_child b ");
                sb.append("where a.id=b.pid and a.ext_part_id=b.part_id ");
                sb.append("and a.id = ? and a.ext_part_id=? ");
                return sb.toString();
            }

            //更新sql
            private String getInsertAddressSql() {
                String sql = "INSERT INTO psb_user_address(name, phone, postcode, province_name, city_name, county_name, province_code, city_code, county_code, address_detail, is_default, user_id, status, create_time, create_user, op_time, op_user, part_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                return sql;
            }
        });
    }


}
